Make postgis raster copy rows configurable

[robe]

A client asked me why does copy mode, always result in

COPY 11

11 rows per copy command.

I checked in the code, and it appears to be a hard-coded setting.

​https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/raster/loader/raster2pgsql.c#L1518 ​https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/raster/loader/raster2pgsql.c#L1845

	if (tileset->length > 10) {
				if (!insert_records(
					config->schema, ovtable, config->raster_column,
					(config->file_column ? config->rt_filename[idx] : NULL), config->file_column_name,
					config->copy_statements, config->out_srid,
					tileset, buffer

I'm assuming this was put in place to get around some copy size limit. Can we get better load by increasing or making this configurable.

I was going to test this out to see. Given that the size of each row would be dependent on tile size, doesn't seem right it should always be 11.

[Regina Obe <lr@…>]

In 2ff0afa/git:

Add Z option to raster2pgsql that allows for specifying number of rows
per SQL COPY. Closes #5170

Add credits for ST_SimplifyPolygonHull to news, References #5169

[Regina Obe <lr@…>]

In a64af1f/git:

Fix copy paste error, should have been atoi instead of atof. References #5170

[komzpa]

echo 'COMMIT;' >> rtpostgis_upgrade.sql
raster2pgsql.c: In function 'build_overview':
raster2pgsql.c:1523:24: error: comparison of integer expressions of different signedness: 'uint32_t' {aka 'unsigned int'} and 'int' [-Werror=sign-compare]
 1523 |    if (tileset->length >= config->max_tiles_per_copy) {
      |                        ^~
raster2pgsql.c: In function 'convert_raster':
raster2pgsql.c:1850:25: error: comparison of integer expressions of different signedness: 'uint32_t' {aka 'unsigned int'} and 'int' [-Werror=sign-compare]
 1850 |     if (tileset->length >= config->max_tiles_per_copy ) {
      |                         ^~
raster2pgsql.c:1973:25: error: comparison of integer expressions of different signedness: 'uint32_t' {aka 'unsigned int'} and 'int' [-Werror=sign-compare]
 1973 |     if (tileset->length >= config->max_tiles_per_copy ) {
      |                         ^~

[robe]

@kompza thanks for catching. I should have looked at the bots.

Thinking about it more, wondering if maybe I should have not introduced a new arg, and just reuse the -Y since -Y takes no args.

so a -Y would be what it was before default to 11. But a -Y <number> would then change the default. I'm going to revise to do that and see how that goes.

[Regina Obe <lr@…>]

In f45fd93/git:

Fix data type of max_tiles_per_copy. References #5170

[robe]

Definitely faster with larger chunks.

Timing for a 2g tif I was testing with 128x128 tiles

# took 6 hrs

raster2pgsql -e -I -Y -t 128x128

# took 1.5 hrs

raster2pgsql -e -I -Y -Z 50000 -t 128x128

# took 1 hrs

raster2pgsql -e -I -Y -Z 100000 -t 128x128

@kompza preferred just ingesting the whole thing as default and using longer switches like —max-tiles-per-copy.

I think switching to long aside from deviating from before is a much larger lift

[Regina Obe <lr@…>]

In ec0ff33/git:

Document new -Z switch. Clarify use of -k. References #5170

[Regina Obe <lr@…>]

In fea02ef/git:

Replace -Z option as optional argument to -Y option.
Increase default of copy to 50 rows from 11. References #5170

[Paul Ramsey <pramsey@…>]

In 6bbcbef1/git:

Replace -Z option as optional argument to -Y option.
Increase default of copy to 50 rows from 11. References #5170

[Regina Obe <lr@…>]

In 7c69026/git:

Update Help screen. Minor adjustments to doc.
References #5170